前言

1. 對專案不熟的情況下，沒有Table Schema
2. 就算對專案熟，也有Table Schema，但根本沒有，也不想花時間去看Table schema
3. 光看到一大堆Table，就不想找了
4. 綜合上述，總之就是懶得找

要怎麼在茫茫資料庫內尋找某個特定字串在哪邊有被使用?

指引你的希望之光就在這！

SQL查詢語法

搜尋有關Willian這個資料欄位

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'Willian'
 
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
    WHILE @TableName IS NOT NULL
     
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )
 
        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
     
            IF @ColumnName IS NOT NULL
             
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END
 
    SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results

查詢結果

第一欄包含資料表與資料欄位名稱，第二欄是資料內容，只要有包含Willian的資料都會被搜尋到，此結果可以看到會員資料表有個帳號就是Willian。

#參考連結
Find a value anywhere in a database